from tw.tool import jl
from tw.tool import Access
from tw.tool import Sqlite
import config as cfg
import pandas as pd

jl=jl()
dateiv,datetpe,shift,datef = jl.get_current_shift_info()
print(dateiv,datetpe,shift,datef)

db_manager = Sqlite(cfg.db_path)
sss,s=jl.get_shiduan_sql()
if db_manager.connect():
    try:
        df = pd.read_sql_query(cfg.getdb_all_sql, db_manager.conn)
        shisuan=pd.DataFrame()
        for i in range(0,49):
            id_value = df["id"][i]#获取id
            line = df["line"][i]#获取线
            ip = df["ip"][i]#获取ip
            db_path = df["db_path"][i]#获取数据库路径
            enabled = df["enabled"][i]#获取是否启用
            size = df["size"][i]#获取数据库大小
            try:
                if size=="半片":
                    access_path = f"\\\{ip}{db_path}\\{datetpe}-{line}-{shift}.mdb"#线别数据库路径
                else:
                    access_path = f"\\\{ip}{db_path}\\{dateiv}-{line}-{shift}.mdb"#线别数据库路径
                print(access_path)
                if enabled==0:
                    print(f"第 {i} 行: {line}, 未启用")
                    continue
                else:
                    print(f"第 {i} 行: {line}, 启用")
                    access_db_manager = Access(access_path) 
                    if access_db_manager.connect():
                        df1 = pd.read_sql_query(sss, access_db_manager.conn)
                        df1['BinFileName'] = df1['BinFileName'].str.split('-S7').str[0]
                        shisuan=pd.concat([shisuan,df1],ignore_index=True)
                        pass
            except Exception as e:
                print(f"第 {i} 行: {line}, 数据库连接失败:")
                # print(f"第 {i} 行: {line}, 数据库连接失败: {e}")
                continue

        if not shisuan.empty:
            grouped = shisuan.groupby(['BinFileName', 'Class']).agg({
                'AvgEta': lambda x: sum(x * shisuan.loc[x.index, 'RecordCount']) / sum(shisuan.loc[x.index, 'RecordCount']),
                'RecordCount': 'sum'
            }).reset_index()
            grouped['Shift'] = s
            grouped['Date'] = datef
            # 将分组计算后的数据写入名为 'shiduan' 的表中，若表已存在则替换，不写入行索引
            # grouped.to_sql('shiduan', db_manager.conn, if_exists='replace', index=False)
            grouped.to_sql('shiduan', db_manager.conn, if_exists='append', index=False)
        else:
            print("shisuan为空")
    except Exception as e:
        print(f"数据库操作失败: {e}")
    finally:
        db_manager.close()